Mapping Chronic Disease to Explore Social Determinants of Health

Background

Chronic disease, or diseases with often life-long medical consequences, are a major issue for Americans. While the cause of the diseases are complex (e.g., is it our diets, infection), a consistent source of differences of disease in the population includes social determinants of health

We often hear that “zipcode is more important than genetic code”. When it comes to where we live, the story is clear but complex as we can define where we live on so many scales, from the country and state to the city and street. In the US, and probably in other parts of the world, there is a clear association between where we live on a “macroscale” (such as state and county) and chronic disease, resulting in “disease belts”, or broad swaths of the nation, such as the southeast, that have higher rates of disease than others. On the other hand, the disparity of disease on the “microscale” -- or within cities -- is complex. Just going over the railroad tracks can reduce the prior risk for disease many fold.

In the following, we show how an important social determinant, where one lives at different scales, from your street, neighborhood, city, zipcode, county, and state, is connected with disease using XY.ai’s integrative Exposome Data Warehouse and software.

There are many types of chronic disease or conditions that result in chronic disease, including heart disease, stroke, and cancer. According to a recent report by Murphy et al, [1], heart disease is the leading cause of death in America, and causes 1 in 4 deaths (heart disease is a broad term that includes many conditions, one of which is coronary heart disease). Cancer is the second leading cause of death in America, with 1.6 million people diagnosed each year, and, stroke results in 1 in 20 deaths in the United States, and there about 795,000 strokes each year [3].

Understanding how these three chronic conditions are distributed geographically in the United States can help the government, healthcare system, and other organizations target prevention programs at those who need it most. For instance, certain geographic “clusters” of high disease rates may indicate that certain environmental exposures may put people at risk for heart disease, stroke or cancer. As well, clusters would indicate where to focus health care system resources, while regions with low disease rates could be examined to adopt best practices to prevent chronic disease.

Here, we demonstrate how to create maps -- from the “macroscale” (or country-scale) to the “city-scale” -- of chronic disease.

Through a few simple queries, we identify disease "belts", or regions where high rates of coronary heart disease, stroke, and cancer tend to cluster. In Part 1, we will do a basic visualization of data from the XY.ai database. Then in Part 2, we'll narrow down on disease belts that have been previously identified in investigations by the CDC and other researchers. Finally, in Part 3 we'll create a choropleth map which color codes US states by the disease rate.

In the next blog post, we’ll look more closely at the correlation between different disease rates, and how the occurrence of diseases “overlap” in different regions. Does the relationship between the increase of obesity correlate with the increase in diabetes? Intuitively that makes sense [link to obesity]. But is obesity also connected to heart disease? Cancer? While high rates of a single disease can be describe as an epidemic, these epidemics can co-occur in an individual or a community, resulting in a “syndemic”, or synergistic epidemic. And how these synergistic epidemics are connected with where we live is critical for us to understand.

Part 1 - Working with XY.ai

Step 1 - Load data from the XY.ai database

To understand how diseases are geographically distributed in the United States, we're going to look at the rates of diseases in different cities across the United States hsing the 500 Cities data from the CDC (https://www.cdc.gov/500cities/index.htm). First, we will connect to XY.ai's Postgres database. This is done using the psycopg2 library, and the connect function. We'll return the results from the query and save it in the cur variable, which will consist of an easy-to-use dictionary.

First, you will need to set up a local copy of the XY test Postgres/PostGIS database:


1. Download all the data files from here: https://github.com/andrewdeoXY/xyblog/tree/master/blog1
2. Edit the SQL script "importxydb.sql" so that the paths on the last 5 lines correspond to the location of the TSV files that you just downloaded in step 1.
3. Then, log into postgres (in Linux: "sudo -u postgres psql template1") and import the SQL script using the Postgres command "\i importxydb.sql"

Now, you will have a small dataset which you can use for the scripts below.

In [1]:
import matplotlib.pyplot as plt
import numpy as np
import psycopg2
from psycopg2.extras import RealDictCursor
import folium
import pandas as pd
import shapely.wkt
from shapely.geometry import mapping, shape
import geojson
from sqlalchemy import func
from collections import defaultdict
#from IPython.display import IFrame
from IPython.display import display_html
    
def GetDBConnection():
    hostDB = "localhost"
    tDB = "template1"
    tUser = "postgres"
    tPassword = ""
    conn = psycopg2.connect(host=hostDB, database=tDB, user=tUser, password=tPassword)
    cur = conn.cursor(cursor_factory=RealDictCursor)    
    return conn, cur
    
# Connect to database and store in dictionary
conn, cur = GetDBConnection()

# Let's get the 500 Cities data stored in the database using an SQL query
cur.execute('Select "CANCER_CrudePrev", "STROKE_CrudePrev", "CHD_CrudePrev", stateabbr, placename, "Geolocation", fips_place_tract from edw.fivehundredcities_data')
my_record = cur.fetchall()

# We've finished querying the database, now close the connections
cur.close()
conn.close()

Step 2 - Process data retrieved from XY.ai database

Now that we've retrieved data from the XY.ai database corresponding to the CDC's 500 Cities data, let's process it further to calculate average disease rates for cities, and then plot those rates as circles of varying size on a map of the United States. We'll store the data in a dictionary called finalData and city coordinates in coordData.

In [15]:
# Processing the data - taking a simple average of the rates for each town
finalData = defaultdict(lambda: defaultdict(float))
cityCancer = defaultdict(float)
cityStroke = defaultdict(float)
cityCHD = defaultdict(float)
coordData = defaultdict(str)
for trow in my_record:
    tid = trow['stateabbr'] + "\t" + trow['placename']
    finalData[tid]['CancerTotal'] += trow['CANCER_CrudePrev']
    finalData[tid]['CancerCount'] += 1
    finalData[tid]['StrokeTotal'] += trow['STROKE_CrudePrev']
    finalData[tid]['StrokeCount'] += 1
    finalData[tid]['CHDTotal'] += trow['CHD_CrudePrev']
    finalData[tid]['CHDCount'] += 1
    coordData[tid] = str(trow['Geolocation'])

# take average
for trow in finalData:
    finalData[trow]['CancerRate'] = finalData[trow]['CancerTotal'] / finalData[trow]['CancerCount']
    finalData[trow]['StrokeRate'] = finalData[trow]['StrokeTotal'] / finalData[trow]['StrokeCount']
    finalData[trow]['CHDRate'] = finalData[trow]['CHDTotal'] / finalData[trow]['CHDCount']

Step 3 - Plot the Results on a Map

Now that we've completed some basic calculations, we can use the mapping library folium to plot the data. To do this, we'll create a map called diseasemap using the folium.Map function, and then iterate through our finalData dictionary and individually plot circles correpsonding to each disease rate for each city.

In [16]:
# Using Folium, plot on a map
diseasemap = folium.Map(location=[35, -100], zoom_start=4.4)

trec = 0
for trow in finalData:
    geoname = trow.split("\t")
    tstate = geoname[0]
    tplacename = geoname[1]
    popup_text = "{}<br> City: {:,}<br> State: {:,}"
    popup_text = "City: " + tplacename + "<br>State: " + tstate
    coord = coordData[trow].replace('(', '')
    coord = coord.replace(')', '')
    ncoord = coord.split(',')
    folium.CircleMarker(location=(float(ncoord[0]), float(ncoord[1])), radius= finalData[trow]['CancerRate'] * 2, color="#007849", popup=popup_text, fill=True).add_to(diseasemap)
    folium.CircleMarker(location=(float(ncoord[0]), float(ncoord[1])), radius= finalData[trow]['StrokeRate'] * 2, color="#ff0000", popup=popup_text, fill=True).add_to(diseasemap)

# Now plot the map
diseasemap
Out[16]:

Step 4 - View the Results

Now we can view the map, and see how cancer and stroke rates vary across the United States. In most cases, the cancer rates are higher than stroke rates. In a minority of cases, like Jackson Mississippi, the cancer and stroke rates are almost the same. In fact many of the southern states have higher rates of stroke (indicated by larger red circles) than in northern states. This rough result hints at the existence of the disease belts noted previously. Let's take a closer look at those belts by subgrouping our data.

Part 2 - Analyzing the Disease Belts

Step 1 - Identify stroke-belt data from our XY.ai dataset

The existence of diabetes and cancer belts has previously been documented in scientific literature [4]. The stroke belt is defined as consisting of Alabama, Arkansas, Florida, Georgia, Kentucky, Louisiana, Mississippi, North Carolina, Ohio, Pennsylvania, South Carolina, Tennessee, Texas, Virginia, and West Virginia [5]. Using our data, is it possible to identify this belt computationally? The answer is (of course!) yes: if we retrieve stroke data and then rank it by the top 40 rates, we should see cities in the stroke belt appear, and the rest of the map would be blank. To do this, first we'll pick the highest rates for every city in the 500 Cities database for cancer, stroke, and CHD:

In [17]:
# prepare data using the highest stroke / cancer rates for each city
strokedata = defaultdict(float)
cancerdata = defaultdict(float)
chddata = defaultdict(float)
for trow in my_record:
    tid = trow['stateabbr'] + "\t" + trow['placename']
    if trow['CANCER_CrudePrev'] > cancerdata[tid]:
        cancerdata[tid] = trow['CANCER_CrudePrev']
    if trow['STROKE_CrudePrev'] > strokedata[tid]:
        strokedata[tid] = trow['STROKE_CrudePrev']
    if trow['CHD_CrudePrev'] > chddata[tid]:
        chddata[tid] = trow['CHD_CrudePrev']

Now we can plot the data using our dictionaries in a similar fashion as we did previously. We'll write a function (called "MakeBeltMap") which will help us display a separate map for cancer, stroke, and CHD:

In [18]:
# The MakeBeltMap function will allow us to extract the 40 highest rates and put them on a map. We should see
# the "stroke belt" emerge, that is a set of states in the south between Texas and Florida
def MakeBeltMap(tdata, beltmap, coordData):
    
    rankcutoff = 40
    tcnt = 0
    for trow in sorted(tdata, key=tdata.get, reverse=True):
        if tcnt < rankcutoff:
            geoname = trow.split("\t")
            tstate = geoname[0]
            tplacename = geoname[1]
            popup_text = "{}<br> City: {:,}<br> State: {:,}"
            popup_text = "City: " + tplacename + "<br>State: " + tstate
            coord = coordData[trow].replace('(', '')
            coord = coord.replace(')', '')
            ncoord = coord.split(',')
            tcolor = "#007849"
            # now, let's color code states that are known to be part of the "stroke belt"
            # Alabama, Arkansas, Florida, Georgia, Kentucky, Louisiana, Mississippi, North Carolina, Ohio, Pennsylvania, South Carolina, Tennessee, Texas, Virginia, and West Virginia
            if (tstate == "AL") | (tstate == "AR") | (tstate == "FL") | (tstate == "GA") | (tstate == "KY") | (tstate == "LA"):
                tcolor = "#ff0000"
            if (tstate == "MS") | (tstate == "NC") | (tstate == "OH") | (tstate == "PA") | (tstate == "SC") | (tstate == "TN"):
                tcolor = "#ff0000"
            if (tstate == "TX") | (tstate == "VA") | (tstate == "WV"):
                tcolor = "#ff0000"

            folium.CircleMarker(location=(float(ncoord[0]), float(ncoord[1])), radius= tdata[trow], color=tcolor, popup=popup_text, fill=True).add_to(beltmap)
        tcnt += 1
    
    return(beltmap)

# Now plot the map of highest stroke rates using the MakeBeltMap function
strokebelt = folium.Map(location=[35, -100], zoom_start=4.4)
strokebelt = MakeBeltMap(strokedata, strokebelt, coordData)
strokebelt
Out[18]:

We can also perform this analysis using cancer data:

In [19]:
# Now plot the map of highest cancer rates
cancerbelt = folium.Map(location=[35, -100], zoom_start=4.4)
cancerbelt = MakeBeltMap(cancerdata, cancerbelt, coordData)
cancerbelt
Out[19]:

And finally using CHD data:

In [20]:
# Plot a map of highest CHD rates
chdbelt = folium.Map(location=[35, -100], zoom_start=4.4)
chdbelt = MakeBeltMap(chddata, chdbelt, coordData)
chdbelt
Out[20]:

Part 3 - Creating a Choropleth

Choropleth maps are very good at allowing users to visualize medical rate data: they provide information about the geographic space affected, and the color scale immediately indicates the rate or other measure used. The previous analyses we performed can also be visualized as a choropleth as follows:

Step 1 - Prepare the Data

In [21]:
# Choropleth map - preparing the data

# Let's get the GeoJSON data for each state from the Folium Git repo: 
# https://github.com/python-visualization/folium/blob/master/examples/data/us-states.json
tJSON = "us-states.json"

# We will need to average disease rates by state, rather than city:
choroCalc = defaultdict(lambda: defaultdict(float))
cancerChoro = defaultdict(float)
strokeChoro = defaultdict(float)
chdChoro = defaultdict(float)
for trow in my_record:
    tid = trow['stateabbr']
    choroCalc[tid]['CancerTotal'] += trow['CANCER_CrudePrev']
    choroCalc[tid]['CancerCount'] += 1
    choroCalc[tid]['StrokeTotal'] += trow['STROKE_CrudePrev']
    choroCalc[tid]['StrokeCount'] += 1
    choroCalc[tid]['CHDTotal'] += trow['CHD_CrudePrev']
    choroCalc[tid]['CHDCount'] += 1

# take average
for trow in choroCalc:
    cancerChoro[trow] = choroCalc[trow]['CancerTotal'] / choroCalc[trow]['CancerCount']
    strokeChoro[trow] = choroCalc[trow]['StrokeTotal'] / choroCalc[trow]['StrokeCount']
    chdChoro[trow] = choroCalc[trow]['CHDTotal'] / choroCalc[trow]['CHDCount']

# Now make a Pandas dataframe from our previous dictionary:
strokedf = pd.DataFrame(list(strokeChoro.items()), columns=['id', 'rate'])
cancerdf = pd.DataFrame(list(cancerChoro.items()), columns=['id', 'rate'])
chddf = pd.DataFrame(list(chdChoro.items()), columns=['id', 'rate'])

Now that we've prepared our data, we can plot it using folium as follows:

In [45]:
# Initialize the map:
strokechoromap = folium.Map(location=[35, -100], zoom_start=4.3)
 
# Add the color for the chloropleth:
folium.Choropleth(geo_data=tJSON, name='choropleth', data=strokedf, columns=['id', 'rate'], key_on='feature.id', fill_color='YlOrRd',
 fill_opacity=0.7, line_opacity=0.2, legend_name='Stroke Rate').add_to(strokechoromap)

# ... and display the map
strokechoromap
Out[45]:

From the above map, the stroke belt becomes exceedingly obvious using the choropleth visualization. Similar maps can also be easily generated for cancer and CHD, with cancer illustrated below:

In [46]:
# Initialize the map:
cancerchoromap = folium.Map(location=[35, -100], zoom_start=4.3)
 
# Add the color for the chloropleth:
folium.Choropleth(geo_data=tJSON, name='choropleth', data=cancerdf, columns=['id', 'rate'], key_on='feature.id', fill_color='YlOrRd',
 fill_opacity=0.7, line_opacity=0.2, legend_name='Stroke Rate').add_to(cancerchoromap)

# ... and display the map
cancerchoromap
Out[46]:

While this information is interesting, it's possible to use XY.ai to generate more high resolution data, or generate choropleths at the census tract level. To do this, we will need to retrieve information from the data warehouse corresponding to the census tract GeoJSON data as follows (we'll restrict our query to Georgia, which is in the stroke belt):

In [2]:
# Connect to database and get GeoJSON data
conn2, cur2 = GetDBConnection()

# Let's get the 500 Cities census geometry data stored in the database using an SQL query
# We will need to convert the data stored in WKB format (encoded as hexidecimal) into WKT format using ST_AsText
cur2.execute('Select ST_AsText(geometrywkt), placename, stateabbr, fips_place_tract from edw.shapefile_fivehundredcities_census_tracts WHERE (placename = \'Boston\' OR placename = \'Miami\' OR placename = \'Atlanta\')')
my_json_record = cur2.fetchall()

features = []
for tid in my_json_record:
    wktval = tid['st_astext']
    tgeometry = shapely.wkt.loads(wktval)
    tgeometry = tgeometry.simplify(0.002, preserve_topology=True)
    properties = {'fips_place_tract': tid['fips_place_tract']}
    features.append(geojson.Feature(geometry=tgeometry, properties=properties))

tJSON = geojson.FeatureCollection(features)

# We've finished querying the database, now close the connections
cur2.close()
conn2.close()

After we've created our GeoJSON data, we can now get our crude prevalence rates and convert them to Pandas dataframes for plotting with folium:

In [3]:
cancerChoroTract = defaultdict(float)
strokeChoroTract = defaultdict(float)
chdChoroTract = defaultdict(float)

for trow in my_record:
    tid = trow['fips_place_tract']
    cancerChoroTract[tid] = trow['CANCER_CrudePrev']
    strokeChoroTract[tid] = trow['STROKE_CrudePrev']
    chdChoroTract[tid] = trow['CHD_CrudePrev']

# Now make a Pandas dataframe from our previous dictionary:
chorostrokedf = pd.DataFrame(list(strokeChoroTract.items()), columns=['id', 'rate'])
chorocancerdf = pd.DataFrame(list(cancerChoroTract.items()), columns=['id', 'rate'])
chorochddf = pd.DataFrame(list(chdChoroTract.items()), columns=['id', 'rate'])

And finally we can plot our folium map using our GeoJSON data and Pandas dataframes containing crude rates:

In [4]:

chorocensus = folium.Map(location=[33.5, -83], zoom_start=8)
 
# Add the color for the chloropleth:
folium.Choropleth(geo_data=tJSON, name='Census Choropleth', data=chorocancerdf, columns=['id', 'rate'], key_on='feature.properties.fips_place_tract', fill_color='YlOrRd',
 fill_opacity=0.7, line_opacity=0.2, legend_name='Cancer Rate').add_to(chorocensus)

# ... and display the map
chorocensus
Out[4]:

In our next step, calculate the difference between the 500 Cities rate data from 2013 and 2015-2016. This will give us an idea of what areas are changing for the better or for the worse.

In [5]:
# Connect to database and get GeoJSON data
conn3, cur3 = GetDBConnection()

# Let's get the 500 Cities data stored in the database using an SQL query - from edw.fivehundredcities_data_2015_2016
cur3.execute('Select "CANCER_CrudePrev", "STROKE_CrudePrev", "CHD_CrudePrev", "fips_place_tract" from edw.fivehundredcities_data_2015_2016')
datafrom2016 = cur3.fetchall()

# Now subtract this data from our 2013 data:
cancerDiff = defaultdict(float)
strokeDiff = defaultdict(float)
chdDiff = defaultdict(float)
for trow in datafrom2016:
    tid = trow['fips_place_tract']
    cancerDiff[tid] = trow['CANCER_CrudePrev'] - cancerChoroTract[tid]
    strokeDiff[tid] = trow['STROKE_CrudePrev'] - strokeChoroTract[tid]
    chdDiff[tid] = trow['CHD_CrudePrev'] - chdChoroTract[tid]

# Now make a Pandas dataframe from our previous dictionary:
strokediff = pd.DataFrame(list(cancerDiff.items()), columns=['id', 'rate'])
cancerdiff = pd.DataFrame(list(strokeDiff.items()), columns=['id', 'rate'])
chddiff = pd.DataFrame(list(chdDiff.items()), columns=['id', 'rate'])

cur3.close()
conn3.close()

Now, we can display the differences between 2015-2016 and 2013 for Atlanta, and with the choropleth it will become obvious which regions saw an increase in cancer rates:

In [6]:
# now convert my_json_record - https://github.com/python-visualization/folium/issues/1051
diffmap = folium.Map(location=[33.5, -83], zoom_start=8)
 
# Add the color for the chloropleth:
folium.Choropleth(geo_data=tJSON, name='Census Choropleth', data=cancerdiff, columns=['id', 'rate'], key_on='feature.properties.fips_place_tract', fill_color='YlOrRd',
 fill_opacity=0.7, line_opacity=0.2, legend_name='Cancer Rate').add_to(diffmap)

# ... and display the map
diffmap
Out[6]:
In [7]:
# create legend
def MapLegend(tDesc, tTop, tLeft):
    legend_html = '<div style="position: absolute; top: ' + tTop + 'px; left: ' + tLeft + 'px; width: 120px; height: 18px; color: white; background-color: blue;'
    legend_html += 'border:2px solid blue; z-index:9999; font-size:10px;"><b><center>' + tDesc + '</center></b></div>'
    return legend_html

def FoliumMapGrid(tmap, tzoom, tsize, tlocation, tleft, ttop, tpos, tJSON, tdata, tlabel):
    
    labelTop = str(ttop + tsize - 50)
    labelLeft = str(tleft + 10)
    mapa = folium.Map(width=300, height=300, zoom_start=tzoom, location=tlocation, left=tleft, top=ttop, position=tpos)
    if tmap != None:
        mapa = mapa.add_to(tmap)
    mapa.get_root().html.add_child(folium.Element(MapLegend(tlabel, labelTop, labelLeft)))
    folium.Choropleth(geo_data=tJSON, name='Census Choropleth', data=tdata, columns=['id', 'rate'], key_on='feature.properties.fips_place_tract', fill_color='YlOrRd',
     fill_opacity=0.7, line_opacity=0.2, legend_name='Cancer Rate').add_to(mapa)
    
    # the keys overlap in the first plot, so delete them for clarity
    for key in mapa._children:
        if key.startswith('choropleth'):
            for key2 in mapa._children[key]._children:
                if key2.startswith('color_map'):
                    del(mapa._children[key]._children[key2])

    return mapa
    

tzoom = 10
tsize = 300
BostonCoord = [42.36, -71.06]
AtlantaCoord = [33.75, -84.39]
MiamiCoord = [25.7805, -80.18037]

mapa = FoliumMapGrid(None, tzoom, tsize, BostonCoord, 1, 1, 'relative', tJSON, cancerdiff, 'Boston (<i>Cancer</i>)')
mapb = FoliumMapGrid(mapa, tzoom, tsize, AtlantaCoord, 300, 1, 'absolute', tJSON, cancerdiff, 'Atlanta (<i>Cancer</i>)')
mapc = FoliumMapGrid(mapa, tzoom, tsize, MiamiCoord, 600, 1, 'absolute', tJSON, cancerdiff, 'Miami (<i>Cancer</i>)')
mapa
mapb
mapc

mapd = FoliumMapGrid(mapa, tzoom, tsize, BostonCoord, 1, 300, 'absolute', tJSON, strokediff, 'Boston (<i>Stroke</i>)')
mape = FoliumMapGrid(mapb, tzoom, tsize, AtlantaCoord, 300, 300, 'absolute', tJSON, strokediff, 'Atlanta (<i>Stroke</i>)')
mapf = FoliumMapGrid(mapc, tzoom, tsize, MiamiCoord, 600, 300, 'absolute', tJSON, strokediff, 'Miami (<i>Stroke</i>)')
mapd
mape
mapf

mapg = FoliumMapGrid(mapa, tzoom, tsize, BostonCoord, 1, 600, 'absolute', tJSON, chddiff, 'Boston (<i>CHD</i>)')
maph = FoliumMapGrid(mapb, tzoom, tsize, AtlantaCoord, 300, 600, 'absolute', tJSON, chddiff, 'Atlanta (<i>CHD</i>)')
mapi = FoliumMapGrid(mapc, tzoom, tsize, MiamiCoord, 600, 600, 'absolute', tJSON, chddiff, 'Miami (<i>CHD</i>)')
mapg
maph
mapi
Out[7]:

In addition to mapping rates to census tracts, we can also do this for counties. For instance, let's take a look at Massachusetts using the following code. First, we'll query data from the data warehouse, and then extract the state and county codes. Then, we'll extract the boundaries / geometry for the counties in for Massachusetts (corresponding to a state FIP code of 25) and then plot the choropleth.

In [8]:
# Connect to database and store in dictionary
conn, cur = GetDBConnection()

# Let's get the 500 Cities data stored in the database using an SQL query
cur.execute('Select "CANCER_CrudePrev", "STROKE_CrudePrev", "CHD_CrudePrev", "stateabbr", "placename", "Geolocation", "fips_place_tract" from edw.fivehundredcities_data')
my_record = cur.fetchall()

# make sample cancer data
cancerdata = defaultdict(lambda: defaultdict(float))
for trow in my_record:
    if (trow['fips_place_tract'].find('-') != -1):
        tdata = trow['fips_place_tract'].split('-')
        nid = tdata[1][0:5]
        cancerdata[nid]['CancerTotal'] += trow['CANCER_CrudePrev']
        cancerdata[nid]['CancerCount'] += 1

mapdata = defaultdict(float)
for tid in cancerdata:
    mapdata[tid] = cancerdata[tid]['CancerTotal'] / cancerdata[tid]['CancerCount']

# We've finished querying the database, now close the connections
cur.close()
conn.close()

# Connect to database and get GeoJSON data
conn2, cur2 = GetDBConnection()

# Let's get the 500 Cities census geometry data stored in the database using an SQL query
# We will need to convert the data stored in WKB format (encoded as hexidecimal) into WKT format using ST_AsText
tSQL = 'select a.statefip, a.fipcode, ST_AsText(a.geometrywkt), b.placename, '
tSQL += 'count(*) as num_census_tracts from exposome_pici.shapefile a left join edw.shapefile_fivehundredcities_census_tracts_2015_2016 b on (ST_INTERSECTS(a.geometrywkt,b.geometrywkt)) '
tSQL += 'where a.summarylevelid = \'050\' and a.startdate = \'2015-01-01\' and a.statefip = ANY(\'{25}\') '
tSQL += 'group by a.statefip, a.fipcode, a.geometrywkt, b.placename'

cur2.execute(tSQL)
my_json_record = cur2.fetchall()

features = []
for tid in my_json_record:
    wktval = tid['st_astext']
    tgeometry = shapely.wkt.loads(wktval)
    tgeometry = tgeometry.simplify(0.005, preserve_topology=True)
    properties = {'fipcode': tid['fipcode'], 'fip_code_place': tid['statefip'] + tid['fipcode']}
    features.append(geojson.Feature(geometry=tgeometry, properties=properties))

tJSON = geojson.FeatureCollection(features)

# We've finished querying the database, now close the connections
cur2.close()
conn2.close()

# now plot folium map
cancerdf = pd.DataFrame(list(mapdata.items()), columns=['id', 'rate'])
cancerchoromap = folium.Map(location=[42.364758, -71.067421], zoom_start=8)

# Add the color for the chloropleth:
folium.Choropleth(geo_data=tJSON, name='choropleth', data=cancerdf, columns=['id', 'rate'], key_on='feature.properties.fip_code_place', 
    fill_color='YlOrRd', fill_opacity=0.7, line_opacity=0.2, legend_name='Stroke Rate').add_to(cancerchoromap)

# ... and display the map
cancerchoromap
Out[8]:

Conclusions

The XY.ai data warehouse can be used to quickly and easily analyze data from the CDC's 500 cities dataset. With some simple analyses, we were able to identify various disease belts in the United States at the census tract, city, county, and state level. In future XY.ai posts, we will look at more detailed analyses using other datasets in the data warehouse.

References

[1] Murphy SL, Xu J, Kochanek KD, Arias E. Mortality in the United States, 2017. NCHS data brief, no 328. Hyattsville, MD: National Center for Health Statistics; 2018.

[2] CDC, Division of Cancer Prevention and Control At A Glance. https://www.cdc.gov/chronicdisease/resources/publications/aag/dcpc.htm. Downloaded on January 2nd, 2019.

[3] CDC, Stroke Facts. https://www.cdc.gov/stroke/facts.htm. Downloaded on January 2nd, 2019.

[4] Ward and Black, "State and Regional Prevalence of Diagnosed Multiple Chronic Conditions Among Adults Aged ≥18 Years — United States, 2014", MMWR; 2016. https://www.cdc.gov/mmwr/volumes/65/wr/mm6529a3.htm. Downloaded on December 30th, 2019.

[5] Borhani NO. Changes and geographic distribution of mortality from cerebrovascular disease. Am J Public Health Nations Health 1965; 55:673–81.